from os import getenv
import pandas

def find_index(l, s):
    for i, item in enumerate( l ):
        if item[0] == s:
            return i

def sum_and_fill(l, begin, blank):
    '''
    sum from begin to the line before blank
    and fill in the blank
    '''
    l[blank][1] = 0
    for item in l[begin:blank]:
        l[blank][1] += item[1]
        print(item, end=', ')
    print('')
    print(l[blank])
    print('')

def update_current_asset(l):
    '''
        l: asset_dict
        caculate total current asset
        Should be called firstly.
    '''
    index = find_index(l, '流动资产合计')
    sum_and_fill(l,0, index)
    return index

def update_non_current_asset(l):
    '''
        caculate non-current asset
        update_current_asset(l) is nested in. 
    '''
    current_index = update_current_asset(l)
    index = find_index(l, '非流动资产合计')
    sum_and_fill(l, current_index + 1, index)
    return index

def update_asset(l):
    update_non_current_asset(l)
    l[-1][1] = 0
    for item in l[:-1]:
        l[-1][1] += item[1]
    l[-1][1] = round(l[-1][1] / 2, 2)
    print(l[-1])

def update_current_liabilities(l):
    index = find_index(l, '流动负债合计')
    sum_and_fill(l, 0, index)
    return index

def update_non_current_liabilities(l):
    previous_index = update_current_liabilities(l)
    index = find_index(l, '非流动负债合计')
    sum_and_fill(l, previous_index + 1, index)
    return index

def update_liabilities(l):
    update_non_current_liabilities(l)
    index = find_index(l, '负债合计')
    l[index][1] = 0
    for item in l[:index]:
        l[index][1] += item[1]
    l[index][1] = round(l[index][1] / 2, 2)
    print(l[index])
    return index

def update_total_right(l):
    previous_index = update_liabilities(l)
    index = find_index(l, '所有者权益（或股东权益）合计')
    sum_and_fill(l, previous_index + 1, index)
    return index

def update_liabilities_and_right(l):
    index_liabilities = update_liabilities(l)
    index_right = update_total_right(l)
    l[-1][1] = l[index_liabilities][1] + l[index_right][1]
    print(l[-1])


def update_balance_sheet():
    df = pandas.read_excel('data/balance.xlsx', index_col=0)

    def get_balance(s):
        return df['余额'][s]

    asset_dict = ( 
            ['流动资产', 0],
            ['货币资金', get_balance('库存现金') + get_balance('银行存款') + get_balance('其他货币资金')],
            ['交易性金融资产', get_balance('交易性金融资产')],
            ['应收票据', get_balance('应收票据')],
            ['应收账款', get_balance('应收账款')],
            ['预付款项', 0],
            ['应收利息', 0],
            ['应收股利', 0],
            ['其他应收款', get_balance('其他应收款')],
            ['存货', get_balance('原材料') + get_balance('材料成本差异') + get_balance('库存商品') + get_balance('委托加工物资') + get_balance('自制半成品') + get_balance('低值易耗品') + get_balance('基本生产成本') ],
            ['一年到期的非流动资产', 0],
            ['其他流动资产', 0],
            ['流动资产合计', 0],
            ['非流动资产', 0],
            ['可供出售金融资产', 0],
            ['持有至到期投资', 0],
            ['长期应收款', 0],
            ['长期股权投资', 0],
            ['投资性房地产', 0],
            ['固定资产', get_balance('固定资产')-get_balance('累计折旧')],
            ['在建工程', get_balance('在建工程')],
            ['工程物资', 0],
            ['固定资产清理', 0],
            ['生产性生物资产', 0],
            ['油气资产', 0],
            ['无形资产', 0],
            ['开发支出', 0],
            ['商誉', 0],
            ['长期待摊费用', 0],
            ['递延所得税资产', 0],
            ['其他非流动资产', 0],
            ['非流动资产合计', 0],
            ['资产总计', 0]
  )

    update_asset(asset_dict)

    asset_df = pandas.DataFrame(
        asset_dict, columns=['资产', '期末余额']
     )
    # print(asset_df)
    # asset_df = pandas.DataFrame(asset_dict)



    liabilities_dict = ( 
        ['流动负债', 0],
        ['短期借款', get_balance('短期借款')],
        ['交易性金融负债', 0],
        ['应付票据', get_balance('应付票据')],
        ['应付账款', get_balance('应付账款')],
        ['预收款项', 0],
        ['应付职工薪酬', get_balance('应付职工薪酬')],
        ['应交税费', get_balance('应交税费')],
        ['应付利息', 0],
        ['应付股利', 0],
        ['其他应付款', get_balance('其他应付款')],
        ['一年内到期的非流动负债', 0],
        ['其他流动负债', 0],
        ['流动负债合计', 0],
        ['非流动负债：', 0],
        ['长期借款', get_balance('长期借款')],
        ['应付债券', 0],
        ['长期应付款', 0],
        ['专项应付款', 0],
        ['预计负债', 0],
        ['递延所得税负债', 0],
        ['其他非流动负债', 0],
        ['非流动负债合计', 0],
        ['负债合计', 0],
        ['所得者权益（或股东权益）', 0],
        ['实收资本（或股本）', get_balance('实收资本')],
        ['资本公积', 0],
        ['减：库存股', 0],
        ['盈余公积', get_balance('盈余公积')],
        ['未分配利润', 0],
        ['所有者权益（或股东权益）合计', 0],
        ['', 0],
        ['负债和所有者权益（或股东权益）总计', 0]
  )

    update_liabilities_and_right(liabilities_dict)

    liabilities_df = pandas.DataFrame(
        liabilities_dict, columns=['负债和所有者权益（或股东利益）', '期末余额']
    )

    result = (pandas.concat((asset_df, liabilities_df), axis=1))
    result.to_excel('data/资产负债表.xlsx', index=False)




if __name__ == '__main__':
    update_balance_sheet()
